USE SAS
GO

INSERT INTO TR_ACCTGROUP (AccountGroup, GroupDesc)
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'Prospect' AS [Desc]	UNION ALL
	SELECT	2, 'Buying'						UNION ALL
	SELECT	3, 'Customer' 
) AS t
WHERE t.ID NOT IN (SELECT AccountGroup FROM TR_ACCTGROUP)
GO

INSERT INTO TR_ACTIVITYTYPE (ActivityType, TypeDesc)	
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'Meeting' AS [Desc]	UNION ALL
	SELECT	2, 'Lunch'						UNION ALL
	SELECT	3, 'Demo'						UNION ALL
	SELECT	4, 'Phone Call'
) AS t
WHERE t.ID NOT IN (SELECT ActivityType FROM TR_ACTIVITYTYPE)
GO

INSERT INTO TR_ADDRESSTYPE (AddressType, TypeDesc)
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'Home' AS [DESC]		UNION ALL
	SELECT	2, 'Office'
) as t
WHERE t.ID NOT IN (SELECT AddressType FROM TR_ADDRESSTYPE)
GO

INSERT INTO TR_DATAORIGIN	(DataOrigin, OriginDesc)
SELECT ID, [Desc] FROM (
	SELECT	1 as ID, 'SPG' as [Desc]		UNION ALL
	SELECT	2, 'Channel Development'		UNION ALL
	SELECT	3, 'DMR'						UNION ALL
	SELECT	4, 'Customer Referral'			UNION ALL
	SELECT	5, 'Cold Call'					UNION ALL
	SELECT	6, 'CIV Sales'					UNION ALL
	SELECT	7, 'Lead Generation'			UNION ALL
	SELECT	8, 'Other'
) AS T
WHERE T.ID NOT IN (SELECT DataOrigin FROM TR_DATAORIGIN)
GO

INSERT INTO TR_NOTETYPE (NoteType, TypeDesc)
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'Reminder'	AS [DESC]	UNION ALL
	SELECT	2, 'Task'						UNION ALL
	SELECT	3, 'Partner'					UNION ALL
	SELECT	4, 'Opportunity'
) AS T
WHERE T.ID NOT IN (SELECT NoteType FROM TR_NOTETYPE)
GO

INSERT INTO TR_OPPREFERRAL (OppReferral, ReferralDesc)
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'SPG' AS [DESC]			UNION ALL
	SELECT	2, 'Channel Development'			UNION ALL
	SELECT	3, 'DMR'							UNION ALL
	SELECT	4, 'Customer Referral'				UNION ALL
	SELECT	5, 'Cold Call'						UNION ALL
	SELECT	6, 'CIV Sales'						UNION ALL
	SELECT	7, 'Lead Generation'				UNION ALL
	SELECT	8, 'Other'
) AS T
WHERE T.ID NOT IN (SELECT OppReferral FROM TR_OPPREFERRAL)
GO

INSERT INTO TR_OPPSTAGE (OppStage, StageDesc)
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'Discovery' AS [DESC]		UNION ALL
	SELECT	2, 'Eval/Demo'						UNION ALL
	SELECT	3, 'Presentation'					UNION ALL
	SELECT	4, 'Negotiation/Pricing'			UNION ALL
	SELECT	5, 'Closing'
) AS T
WHERE T.ID NOT IN (SELECT OppStage FROM TR_OPPSTAGE)
GO

INSERT INTO TR_OPPTYPE(OppType, TypeDesc) 
SELECT ID, [Desc] FROM (
	SELECT	1 AS ID, 'New' AS [DESC]			UNION ALL
	SELECT	2, 'Added SKU'						UNION ALL
	SELECT	3, 'Existence (RunRate)'			UNION ALL
	SELECT	4, 'Refresh'						UNION ALL
	SELECT	5, 'Service'						UNION ALL
	SELECT	6, 'RFP/RFQ'
) AS T
WHERE T.ID NOT IN (SELECT OppType FROM TR_OPPTYPE)
GO

INSERT INTO TR_PARTNERCATEGORY(PartnerCategory, CategoryDesc)
SELECT ID, [DESC] FROM (
	SELECT	1 AS ID, 'Person' AS [DESC]			UNION ALL
	SELECT	2, 'Organization'
) AS T
WHERE T.ID NOT IN (SELECT PartnerCategory FROM TR_PARTNERCATEGORY)
GO

INSERT INTO TR_PARTNERDIVISION (PartnerDivision, DivisionDesc)	
SELECT ID, [DESC] FROM (
	SELECT	1 AS ID, 'CMD' AS [DESC]			UNION ALL
	SELECT	2, 'BMG'
) AS T
WHERE T.ID NOT IN (SELECT PartnerDivision FROM TR_PARTNERDIVISION)
GO

INSERT INTO TR_PARTNERTYPE(PartnerType, TypeDesc)
SELECT ID, [DESC] FROM (
	SELECT	1 AS ID, 'Parent Account' AS [DESC]	UNION ALL
	SELECT	2, 'Child Account'					UNION ALL
	SELECT	3, 'Contact'						UNION ALL
	SELECT	4, 'User'							UNION ALL
	SELECT	5, 'Reseller'
) AS T
WHERE T.ID NOT IN (SELECT PartnerType FROM TR_PARTNERTYPE)
GO

INSERT INTO TR_RELATIONTYPE	(RelationshipType, TypeDesc)
SELECT ID, [DESC] FROM (
	SELECT	1 AS ID, 'Reseller' AS [DESC]		UNION ALL
	SELECT	2, 'Contact'						UNION ALL
	SELECT	3, 'Person Responsible'				UNION ALL
	SELECT	4, 'Distributor'					UNION ALL
	SELECT	5, 'Manager'						UNION ALL
	SELECT	6, 'Child Account'					UNION ALL
	SELECT	7, 'Parent Account'					UNION ALL
	SELECT	8, 'Account'						UNION ALL
	SELECT	9, 'Activity Partner'				UNION ALL
	SELECT	10, 'Task Partner'					UNION ALL
	SELECT	11, 'Opportunity Partner'
) AS T
WHERE T.ID NOT IN (SELECT RelationshipType FROM TR_RELATIONTYPE)
GO

INSERT INTO TR_STATUS ([Status],StatusDesc,StatusProfile)		
SELECT ID, [DESC1], [DESC2] FROM (
	SELECT	1 AS ID, 'Won' AS DESC1, 'Opportunity' AS DESC2		UNION ALL
	SELECT	2, 'Lost', 'Opportunity'							UNION ALL
	SELECT	3, 'In-Progress', 'Opportunity'						UNION ALL
	SELECT	4, 'Cancelled', 'Opportunity'						UNION ALL
	SELECT	5, 'Open', 'Activity'								UNION ALL
	SELECT	6, 'In-Progress', 'Activity'						UNION ALL
	SELECT	7, 'Open', 'Task'									UNION ALL
	SELECT	8, 'In-Progress', 'Task'							UNION ALL
	SELECT	9, 'Completed', 'Task'								UNION ALL
	SELECT	10, 'Completed', 'Activity'							UNION ALL
	SELECT	11, 'Cancelled', 'Activity'							UNION ALL	
	SELECT	12, 'Cancelled', 'Task'								
) AS T
WHERE T.ID NOT IN (SELECT [Status] FROM TR_STATUS)
GO

INSERT INTO TR_VERTICAL (Vertical, VerticalDesc)
SELECT ID, [DESC] FROM (
	SELECT	1 AS ID, 'Retail/Hospitality' AS [DESC]	UNION ALL
	SELECT	2, 'Healthcare/Pharmaceutical'			UNION ALL
	SELECT	3, 'Corporate'
) AS T
WHERE T.ID NOT IN (SELECT Vertical FROM TR_VERTICAL)
GO


INSERT INTO TR_STATES([State], StateDesc)		
SELECT ID, [DESC] FROM (
	SELECT 'AL' AS [ID], 'ALABAMA' as [DESC]		UNION ALL
	SELECT 'AK', 'ALASKA'							UNION ALL
	SELECT 'AS', 'AMERICAN SAMOA'					UNION ALL
	SELECT 'AZ', 'ARIZONA'							UNION ALL
	SELECT 'AR', 'ARKANSAS'							UNION ALL
	SELECT 'CA', 'CALIFORNIA'						UNION ALL
	SELECT 'CO', 'COLORADO'							UNION ALL
	SELECT 'CT', 'CONNECTICUT'						UNION ALL
	SELECT 'DE', 'DELAWARE'							UNION ALL
	SELECT 'DC', 'DISTRICT OF COLUMBIA' 			UNION ALL
	SELECT 'FM', 'FEDERATED STATES OF MICRONESIA'	UNION ALL
	SELECT 'FL', 'FLORIDA'							UNION ALL
	SELECT 'GA', 'GEORGIA'							UNION ALL
	SELECT 'GU', 'GUAM'								UNION ALL
	SELECT 'HI', 'HAWAII'							UNION ALL
	SELECT 'ID', 'IDAHO'							UNION ALL
	SELECT 'IL', 'ILLINOIS'							UNION ALL
	SELECT 'IN', 'INDIANA'							UNION ALL
	SELECT 'IA', 'IOWA'								UNION ALL
	SELECT 'KS', 'KANSAS'							UNION ALL
	SELECT 'KY', 'KENTUCKY'							UNION ALL
	SELECT 'LA', 'LOUISIANA'						UNION ALL
	SELECT 'ME', 'MAINE'							UNION ALL
	SELECT 'MH', 'MARSHALL ISLANDS'					UNION ALL
	SELECT 'MD', 'MARYLAND'							UNION ALL
	SELECT 'MA', 'MASSACHUSETTS'					UNION ALL
	SELECT 'MI', 'MICHIGAN'							UNION ALL
	SELECT 'MN', 'MINNESOTA'						UNION ALL
	SELECT 'MS', 'MISSISSIPPI'						UNION ALL
	SELECT 'MO', 'MISSOURI'							UNION ALL
	SELECT 'MT', 'MONTANA'							UNION ALL
	SELECT 'NE', 'NEBRASKA'							UNION ALL
	SELECT 'NV', 'NEVADA'							UNION ALL
	SELECT 'NH', 'NEW HAMPSHIRE'					UNION ALL
	SELECT 'NJ', 'NEW JERSEY'						UNION ALL
	SELECT 'NM', 'NEW MEXICO'						UNION ALL
	SELECT 'NY', 'NEW YORK'							UNION ALL
	SELECT 'NC', 'NORTH CAROLINA'					UNION ALL
	SELECT 'ND', 'NORTH DAKOTA'						UNION ALL
	SELECT 'MP', 'NORTHERN MARIANA ISLANDS'			UNION ALL
	SELECT 'OH', 'OHIO'								UNION ALL
	SELECT 'OK', 'OKLAHOMA'							UNION ALL
	SELECT 'OR', 'OREGON'							UNION ALL
	SELECT 'PW', 'PALAU'							UNION ALL
	SELECT 'PA', 'PENNSYLVANIA'						UNION ALL
	SELECT 'PR', 'PUERTO RICO'						UNION ALL
	SELECT 'RI', 'RHODE ISLAND'						UNION ALL
	SELECT 'SC', 'SOUTH CAROLINA'					UNION ALL
	SELECT 'SD', 'SOUTH DAKOTA'						UNION ALL
	SELECT 'TN', 'TENNESSEE'						UNION ALL
	SELECT 'TX', 'TEXAS'							UNION ALL
	SELECT 'UT', 'UTAH'								UNION ALL
	SELECT 'VT', 'VERMONT'							UNION ALL
	SELECT 'VI', 'VIRGIN ISLANDS'					UNION ALL
	SELECT 'VA', 'VIRGINIA'							UNION ALL
	SELECT 'WA', 'WASHINGTON'						UNION ALL
	SELECT 'WV', 'WEST VIRGINIA'					UNION ALL
	SELECT 'WI', 'WISCONSIN'						UNION ALL
	SELECT 'WY', 'WYOMING'				
) AS T
WHERE T.ID NOT IN (SELECT [State] FROM TR_STATES)
GO
